from django.shortcuts import render

# Create your views here.
from booktest.models import BookInfo, HeroInfo
from django.db.models import F, Q, Sum, Avg
from datetime import date

"""演示增加数据  save和create"""
book = BookInfo()
book.btitle = '西游记'
# book.bpub_date = date(1991, 1, 1)
book.bpub_date = '1991-1-1'
book.save()

hero1 = HeroInfo(
    hname='孙悟空',
    hgender=1,
    hcomment='72变化',
    # hbook_id=book.id
    hbook=book
)
hero1.save()


book2 = BookInfo.objects.create(
    btitle='梦三国',
    bpub_date='2011-11-11',
    bread=10,
    bcomment=10
)

"""演示基本查询  get  all  count"""
# try:
#     BookInfo.objects.get(id=1)
# except BookInfo.DoesNotExist:
#     print()

BookInfo.objects.all()

BookInfo.objects.count()


"""演示过滤查询  filter"""
BookInfo.objects.filter(id__exact=1)
BookInfo.objects.filter(id=1)


BookInfo.objects.filter(btitle__contains='湖')
BookInfo.objects.filter(btitle__endswith='部')

BookInfo.objects.filter(btitle__isnull=False)

BookInfo.objects.filter(id__in=[2, 4])  # 查找容器指定的那几个记录

# >gt  >=gte   < lt  <= lte
BookInfo.objects.filter(id__gt=2)

BookInfo.objects.exclude(id=3)

BookInfo.objects.filter(bpub_date__year='1980')

BookInfo.objects.filter(bpub_date__gt='1990-1-1')


"""演示F和Q对象"""
# BookInfo.objects.filter(bread__gt=)
# 当需要和另一个属性进行比较时用F对象来包裹属性
BookInfo.objects.filter(bread__gt=F('bcomment'))
BookInfo.objects.filter(bread__gt=F('bcomment') * 2)

# BookInfo.objects.filter(bread__gt=20, pk__lt=3)
# BookInfo.objects.filter(Q(bread__gt=20) & Q(pk__lt=3))
BookInfo.objects.filter(Q(bread__gt=20) | Q(pk__lt=3))

BookInfo.objects.filter(~Q(id=3))


"""聚合函数"""
BookInfo.objects.aggregate(Sum('bread'))
# {'bread__sum': 136}  {属性名__聚合函数名小写: 值}

"""排序"""
BookInfo.objects.all().order_by('bread')  # 默认升序
BookInfo.objects.all().order_by('-bread')  # 降序



"""关联基本查询"""
# 一查多: 用一的这一方模型对象.多的那方模型类名小写_set
book3 = BookInfo.objects.get(id=1)
book3.heroinfo_set.all()

# 多查一:  用多的这一方模型对象.外键
hero2 = HeroInfo.objects.get(hname='郭靖')
hero2.hbook


"""内联查询/关联过滤查询
想要查谁,就以谁的模型名开头

"""
# 多查一: 拿多的那一方模型来当条件
# BookInfo.objects.filter(heroinfo__hname='郭靖')
# BookInfo.objects.filter(heroinfo__hcomment__contains='龙')


# 一查多: 拿一的那一方模型来当条件
HeroInfo.objects.filter(hbook__btitle='天龙八部')
HeroInfo.objects.filter(hbook__btitle__contains='湖')


"""修改"""
book = BookInfo.objects.get(btitle='西游记')
book.btitle = '西游记后传'
book.save()

BookInfo.objects.filter(btitle='第二版').update(btitle='第二版<<真的第二先上线>>', bread=30, bcomment=40)

"""删除"""
book = BookInfo.objects.get(id=6)
book.delete()

BookInfo.objects.filter(id__gte=5).delete()